package cn.liumouren.controller;


import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import cn.liumouren.entity.ExcelInfo;
import cn.liumouren.entity.Header;
import cn.liumouren.entity.Result;
import cn.liumouren.entity.Task;
import cn.liumouren.pojo.User;
import cn.liumouren.util.POIUtils;
import cn.liumouren.util.RedisUtil;
import com.wuwenze.poi.ExcelKit;
import com.wuwenze.poi.handler.ExcelReadHandler;
import com.wuwenze.poi.pojo.ExcelErrorField;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.Cookie;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedInputStream;
import java.io.File;
import java.io.IOException;
import java.io.Writer;
import java.time.Duration;
import java.util.*;
import java.util.concurrent.TimeUnit;

/**
 * 导入Demo
 * <p>
 * UserEntity是标注注解的类, Excel的导入映射类, onProcess的userEntity参数则是Excel每行数据的映射实体
 * 需要使用本组件的开发者自己定义
 * <p>
 * ErrorEntity是封装了每行Excel数据常规校验后的错误信息实体, 封装了sheet号、行号、列号、单元格值、所属列名、错误信息
 * <p>
 * onProcess方法是用户自己实现, 当经过正则或者判空常规校验成功后执行的方法, 参数是每行数据映射的实体
 * onError方法是用户自己实现, 当经过正则或者判空常规校验失败后执行的方法
 */
@RestController
public class ExcelController {

    @Resource
    private HttpServletRequest request;
    @Autowired
    private RedisTemplate redisTemplate;

    private final int SURVIVAL_TIME = 1; // redis中存活时间 单位hour

    private final String FILE_NAME = "感谢使用";  //导出excel的文件名

    @Value("${excels.path}")
    private String path; //储存excels的路径

    // 生成导入模板
    @RequestMapping(value = "/download", method = RequestMethod.GET)
    public void downTemplate(HttpServletResponse response) {
//        List<User> userList = userService.find();
        /**
         *  获得cookie 为 llw 的值, 作为唯一标识在redis里找
         *      如果没有cookie不存在name为llw
         *          导出模板
         *      如果存在
         *          获得cookieValue在redis中查找该用户对应的task
         */
        Cookie[] cookies = request.getCookies();
        if (cookies != null && cookies.length > 0) {
            for (Cookie cookie : cookies) {
                if ("llw".equals(cookie.getName())) {
                    //有llw的cookie
                    String cookieValue = cookie.getValue();
                    Task taskInRedis = (Task) redisTemplate.opsForValue().get(cookieValue);
                    if (taskInRedis != null) {
                        //清空redis和cookie
                        redisTemplate.delete(cookieValue);
                        cookie.setMaxAge(0);
                        cookie.setPath("/");
                        response.addCookie(cookie);
                        //在redis里有值
                        ExcelKit.$Export(User.class, response).downXlsx(taskInRedis.getList(), true);
                        return;
                    } else {
                        //在redis里无值
                        break;
                    }
                }
            }
        }
        //无cookie,导出空模板
        ExcelKit.$Export(User.class, response).downXlsx(new ArrayList<User>(), true);
    }

    @RequestMapping(value = "/getTemplate", method = RequestMethod.GET)
    public void getTemplate(HttpServletResponse response) {
        //导出空模板
        ExcelKit.$Export(User.class, response).downXlsx(new ArrayList<User>(), true);
    }

    @RequestMapping(value = "/import", method = RequestMethod.POST)
    public Result importUser(MultipartFile excelFile, HttpServletResponse response) throws IOException {
        long beginMillis = System.currentTimeMillis();

        List<User> successList = new ArrayList<>();
        List<Map<String, Object>> errorList = new ArrayList<>();

        ExcelKit.$Import(User.class).readXlsx(excelFile.getInputStream(), new ExcelReadHandler<User>() {

            @Override
            public void onSuccess(int sheetIndex, int rowIndex, User user) {
                successList.add(user); // 单行读取成功，加入入库队列。
            }

            @Override
            public void onError(int sheetIndex, int rowIndex, List<ExcelErrorField> errorFields) {
                // 读取数据失败，记录了当前行所有失败的数据
                Map<String, Object> map = new HashMap<>();
                map.put("sheetIndex", sheetIndex);
                map.put("rowIndex", rowIndex);
                map.put("errorFields", errorFields);
                errorList.add(map);
            }
        });

        /**
         * 客户端获取模板
         *      客户端上传excel
         *          判断是否时第一次上传(是否有 llw 的cookie)
         *              是
         *                  生成一个独立的Task对象
         *              不是
         *                  找到该用户所拥有的Task对象
         *                  往Task对象里添加数据
         *
         */
        int total;  //总数据条数
        String cookieValue = checkCookie("llw", request);
        boolean hasCookie = cookieValue != null;

        String value = System.currentTimeMillis() + "";   //每个用户的唯一标识

        //没有cookie
        if (!hasCookie) {
            //往客户端发送cookie
            Cookie cookie = new Cookie("llw", value);
            cookie.setPath("/");
            response.addCookie(cookie);
            //生成该客户端唯一Task
            Task task = new Task();
            task.setList(successList);   //将读取成功的list放入task
            total = successList.size();  //当前所有条数
            //将task存入redis 1个小时
            redisTemplate.opsForValue().set(value, task, SURVIVAL_TIME, TimeUnit.HOURS);
        } else {
            //有cookie
            //根据cookieValue找到该用户的唯一Task
            Task task = (Task) redisTemplate.opsForValue().get(cookieValue);
            //将successList传入taskList
            List<User> taskList = task.getList();
            taskList.addAll(successList);
            task.setList(taskList);
            total = taskList.size();   //更新条数
            //存入redis
            redisTemplate.opsForValue().set(cookieValue, task, SURVIVAL_TIME, TimeUnit.HOURS);
        }

        // 执行successList的入库操作。
        Map<String, Object> map = new HashMap<>();
        map.put("data", successList);
        map.put("haveError", errorList.size() > 0);
        map.put("error", errorList.size());
        map.put("timeConsuming", (System.currentTimeMillis() - beginMillis) / 1000L);


        return new Result(true, "导入成功，当前已有" + total + "条数据，" + errorList.size() + "条数据没有导入成功", map);
    }


    /*
    自定义导入
     */
    @PostMapping("/auto")
    public synchronized Result importData(MultipartFile excelFile, int skipCount) throws IOException {

        List<String[]> excel = POIUtils.readExcel(excelFile, skipCount);

        String cookieValue = checkCookie("daniel", request);
        boolean hasCookie = cookieValue != null;

        if (hasCookie) {
            //如果redis里还没有这个key,第一次导入, 创建该key
            if (!redisTemplate.hasKey(cookieValue)) {
                Task task = new Task(cookieValue);
                XSSFWorkbook sheets = new XSSFWorkbook(excelFile.getInputStream());
                XSSFSheet sheet = sheets.getSheetAt(0);
                String title = sheet.getRow(0).getCell(0).getStringCellValue();
                task.setData(excel);
                task.setDataTitle(title);
                task.setDataTitleCount(skipCount);
                task.setFilename(excelFile.getOriginalFilename());
                redisTemplate.opsForValue().set(cookieValue, task, Duration.ofHours(SURVIVAL_TIME));
                return new Result(true, "导入成功, 已有" + (excel.size() - 1) + "条数据");
            } else {
                Task task = (Task) redisTemplate.opsForValue().get(cookieValue);
                if (task != null) {
                    List<String[]> data = task.getData();
                    excel.remove(0); //去除头部
                    data.addAll(excel);
                    task.setData(data);
                    redisTemplate.opsForValue().set(cookieValue, task, Duration.ofHours(SURVIVAL_TIME));
                    return new Result(true, "导入成功, 已有" + (data.size() - 1) + "条数据");
                }
                return new Result(false, "导入失败,请关闭浏览器重试");
            }
        } else {
            //没有cookie
            return new Result(false, "数据导出之后请刷新一下页面哦");
        }

    }

    /*@GetMapping("/autodownload")
    public String autoDownload(HttpServletResponse response) throws IOException {

        Cookie[] cookies = request.getCookies();
        if (cookies != null && cookies.length > 0) {
            for (Cookie cookie : cookies) {
                if ("daniel".equals(cookie.getName())) {
                    Task task = (Task) redisTemplate.opsForValue().get(cookie.getValue());
                    if (task != null) {
                        //生成excel, 并导出
                        List<String[]> data = task.getData();
                        String[] head = data.get(0);  //获取head

                        XSSFWorkbook excelSheets = new XSSFWorkbook();
                        XSSFSheet sheet = excelSheets.createSheet();
                        //创建表头
                        XSSFRow row = sheet.createRow(0);
                        for (int i = 0; i < head.length; i++) {
                            XSSFCell cell = row.createCell(i);
                            cell.setCellValue(head[i]);
                        }
                        //填充数据
                        for (int i = 1; i < data.size(); i++) {
                            XSSFRow r = sheet.createRow(i);
                            for (int j = 0; j < head.length; j++) {
                                XSSFCell cell = r.createCell(j);
                                cell.setCellValue(data.get(i)[j]);
                            }
                        }
                        //数据填充完,清空redis和cookie
                        redisTemplate.delete(cookie.getValue());
                        cookie.setPath("/"); //路径要相同
                        cookie.setMaxAge(0); //生命周期设置为0
                        response.addCookie(cookie);

                        OutputStream out = null;
                        try {
                            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
                            response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(FILE_NAME, "UTF-8") + ".xlsx");
                            out = response.getOutputStream();
                            excelSheets.write(out);
                        } catch (IOException e) {
                            e.printStackTrace();
                        } finally {
                            excelSheets.close();
                            if (out != null) {
                                out.close();
                            }
                        }
                        return "<h1>导出数据完成, 请完成下载excel文件</h1>";
                    } else {
                        //有cookie但是没有导入数据
                        return "<h1>你还没有导入任何数据哦~</h1>";
                    }
                }
            }
        }
        //没有daniel的cookie或者没有cookie
        return "<h1>你还没有导入任何数据哦~</h1>";
    }*/

    @GetMapping("/autodownload")
    public String autoDownload(HttpServletResponse response) throws IOException {

        String cookieValue = checkCookie("daniel", request);
        //有daniel的cookie
        if (cookieValue != null) {
            Task task = (Task) redisTemplate.opsForValue().get(cookieValue);
            //有daniel的cookie但是还没有导入数据
            if (task == null) {
                return "<h1>你还没有导入任何数据哦~</h1>";
            }
            //有daniel的cookie并且导入了数据
            List<String[]> data = task.getData();
            String[] head = data.remove(0);//去除表头
            XSSFWorkbook excelSheets = POIUtils.writeToExcel(data, head, task.getDataTitle(), task.getDataTitleCount());

            //数据填充完,清空redis和cookie
            redisTemplate.delete(cookieValue);
            Cookie cookie = new Cookie("daniel", cookieValue);
            cookie.setPath("/"); //路径要相同
            cookie.setMaxAge(0); //生命周期设置为0 (清除cookie)
            response.addCookie(cookie);

            POIUtils.download(response, excelSheets, task.getFilename());

            return "<h1>导出数据完成, 请完成下载excel文件</h1>";
        } else {
            //没有daniel的cookie
            return "<h1>你还没有导入任何数据哦~</h1>";
        }

    }


    //手动批量上传
    /*
    自定义导入
     */
    @PostMapping("/shouDongShangChuan")
    public Result shouDongShangChuan(List<MultipartFile> files, HttpServletResponse response) throws IOException {
        //判断是不是第一次导入
        String cookieValue = checkCookie("daniel", request);
        boolean hasCookie = cookieValue != null;
        //没有cookie第一次导入
        if (!hasCookie) {
            // 选取第一个excel的表头作为总的表头
            String[] header = POIUtils.readExcel(files.get(0)).get(0);
            List<String[]> allData = new ArrayList<>();  //所有data数据
            allData.add(header);
            Task task = new Task();
            String id = System.currentTimeMillis() + "";  //当前时间作为唯一标识
            for (MultipartFile excelFile : files) {
                List<String[]> eachExcelData = POIUtils.readExcel(excelFile);
                eachExcelData.remove(0);  //删除表头数据
                allData.addAll(eachExcelData);
            }
            task.setData(allData);
            redisTemplate.opsForValue().set(id, task, SURVIVAL_TIME, TimeUnit.HOURS);
            Cookie cookie = new Cookie("daniel", id);
            cookie.setPath("/");
            //cookie默认关闭浏览器就消失
            response.addCookie(cookie);
            return new Result(true, "上传成功，已有" + (allData.size() - 1) + "条数据");
        }
        //有cookie
        Task task = (Task) redisTemplate.opsForValue().get(cookieValue);
        if (task != null) {
            List<String[]> data = task.getData();
            List<String[]> curDataList = new ArrayList<>();
            for (MultipartFile excel : files) {
                List<String[]> curData = POIUtils.readExcel(excel);
                curData.remove(0);
                curDataList.addAll(curData);
            }
            data.addAll(curDataList);
            task.setData(data);
            redisTemplate.opsForValue().set(cookieValue, task, SURVIVAL_TIME, TimeUnit.HOURS);
            return new Result(true, "上传成功，已有" + (data.size() - 1) + "条数据");
        }
        //有daniel的cookie ,但是redis中已经过期
        return new Result(false, "请关闭浏览器重新打开再次尝试!");
    }

    //判断当前请求是否含有指定cookieName的cookie, 如果有返回指定cookieName的值, 没有返回null
    private String checkCookie(String cookieName, HttpServletRequest request) {
        boolean hasCookie = false;
        Cookie[] cookies = request.getCookies();
        String cookieValue = "";
        if (cookies != null && cookies.length > 0) {
            String cookiename;
            for (Cookie cookie : cookies) {
                cookiename = cookie.getName();
                //有cookie
                if (cookieName.equals(cookiename)) {
                    hasCookie = true;
                    cookieValue = cookie.getValue();
                    break;
                }
            }
        }
        return hasCookie ? cookieValue : null;
    }

    //获取一个唯一标识符
    @GetMapping("/id")
    public Result getId(HttpServletResponse response) {
        String cookieValue = checkCookie("daniel", request);
        boolean hasCookie = cookieValue != null;
        if (hasCookie) {
            return new Result(true, "成功获取当前id", cookieValue);
        }
        String id = UUID.randomUUID().toString();
        Cookie cookie = new Cookie("daniel", id);
        cookie.setPath("/");
        response.addCookie(cookie);
        return new Result(true, "成功获取一个id", id);
    }

    // 重置id
    @GetMapping("/refreshId")
    public Result refreshId(HttpServletResponse response) {
        String id = UUID.randomUUID().toString();
        Cookie cookie = new Cookie("daniel", id);
        cookie.setPath("/");
        response.addCookie(cookie);
        return new Result(true, "成功获取一个id", id);
    }


    @PostMapping("/auto1")
    public Result importData1(MultipartFile excelFile, int skipCount) throws IOException {

//        Resource resource = ResourceUtil.getResourceObj("excels");
//        String path = resource.getUrl().getPath();
//        String path = "/root/mydata/excels"; //linux系统里
        String randomName = File.separator + UUID.randomUUID().toString() + ".xlsx";

        List<List<Object>> data = new ArrayList<>();
        try (ExcelReader reader = ExcelUtil.getReader(new BufferedInputStream(excelFile.getInputStream()))) {
            data = reader.read(skipCount + 1);  //跳过title和header
        } catch (IOException e) {
            e.printStackTrace();
        }

        String cookieValue = checkCookie("daniel", request);
        boolean hasCookie = cookieValue != null;

        if (hasCookie) {
            //如果redis里还没有这个key,第一次导入将导入的excel复制到本地
            synchronized (ExcelController.class) {
                Boolean hasKey = redisTemplate.hasKey(cookieValue);
                hasKey = hasKey != null && hasKey;

                if (!hasKey) {
                    FileUtil.touch(path + randomName); //创建一个xlsx

                    BufferedInputStream bis = new BufferedInputStream(excelFile.getInputStream());
                    FileUtil.writeFromStream(bis, path + randomName);  //将导入的excel复制到了本地

                    Task task = new Task(cookieValue);
                    task.setData1(data);
//                    task.setPath("excels" + randomName);
                    task.setPath(path + randomName);  //linux系统里
                    task.setDataTitleCount(skipCount);
                    task.setFilename(excelFile.getOriginalFilename());
                    redisTemplate.opsForValue().set(cookieValue, task, Duration.ofHours(SURVIVAL_TIME));

                    bis.close();
                    return new Result(true, "导入成功, 已有" + data.size() + "条数据");
                } else {
                    Task task = (Task) redisTemplate.opsForValue().get(cookieValue);
                    if (task != null) {
                        List<List<Object>> allData = task.getData1();
                        allData.addAll(data);
                        task.setData1(allData);
                        redisTemplate.opsForValue().set(cookieValue, task, Duration.ofHours(SURVIVAL_TIME));
                        return new Result(true, "导入成功, 已有" + allData.size() + "条数据");
                    }
                    return new Result(false, "导入失败,请关闭浏览器重试");
                }
            }
        } else {
            //没有cookie
            return new Result(false, "数据导出之后请刷新一下页面哦");
        }

    }

    @PostMapping("/manualImport")
    public Result manualImport(MultipartFile excelFile, int skipCount) throws IOException {
        String filename = File.separator + UUID.randomUUID().toString() + ".xlsx";
        String fullPath = path + filename;

        List data = new ArrayList<>();
        try (ExcelReader reader = ExcelUtil.getReader(new BufferedInputStream(excelFile.getInputStream()))) {
            data = reader.read(skipCount + 1);  //跳过title和header
        } catch (IOException e) {
            e.printStackTrace();
        }

        String cookieValue = checkCookie("daniel", request);
        boolean hasCookie = cookieValue != null;

        if (hasCookie) {
            //如果redis里还没有这个key,第一次导入将导入的excel复制到本地
            synchronized (ExcelController.class) {
                Boolean hasKey = redisTemplate.hasKey("data:" + cookieValue);
                hasKey = hasKey != null && hasKey;

                if (!hasKey) {
                    FileUtil.touch(fullPath); //创建一个xlsx
                    BufferedInputStream bis = new BufferedInputStream(excelFile.getInputStream());
                    FileUtil.writeFromStream(bis, fullPath);  //将导入的excel复制到了本地

                    ExcelInfo excelInfo = new ExcelInfo();
                    excelInfo.setId(cookieValue);
                    excelInfo.setFilename(excelFile.getOriginalFilename());
                    excelInfo.setPath(fullPath);
                    Header header = new Header();
                    header.setDataTitleCount(skipCount);
                    excelInfo.setHeader(header);

                    // 将头信息和数据存入redis
                    RedisUtil.set("info:" + excelInfo.getId(), excelInfo, Duration.ofMinutes(60));
                    RedisUtil.rightPushAll("data:" + excelInfo.getId(), data, 60, TimeUnit.MINUTES);

                    bis.close();
                    return new Result(true, "导入成功, 已有" + data.size() + "条数据");
                } else {
                    RedisUtil.rightPushAll("data:" + cookieValue, data, 60, TimeUnit.MINUTES);
                    return new Result(true, "导入成功, 已有" + RedisUtil.sizeOfList("data:" + cookieValue) + "条数据");
                }
            }
        } else {
            //没有cookie
            return new Result(false, "数据导出之后请刷新一下页面哦");
        }

    }

    @GetMapping("/manualDownload")
    public String manualDownload(HttpServletResponse response) throws IOException {
        String cookieValue = checkCookie("daniel", request);
        //有daniel的cookie
        if (cookieValue != null) {
            List<List<Object>> data = RedisUtil.range("data:" + cookieValue, 0, -1);
            //有daniel的cookie但是还没有导入数据
            if (CollUtil.isEmpty(data)) {
                return "<h1>你还没有导入任何数据哦~</h1>";
            }
            ExcelInfo info = RedisUtil.get("info:" + cookieValue);
            if (info == null) {
                return "<h1>操作时间太长了哟, 请刷新浏览器重试~</h1>";
            }
            //有daniel的cookie并且导入了数据
            try(ExcelWriter writer = ExcelUtil.getWriter(info.getPath());) {
                //linux系统里
                writer.passRows(info.getHeader().getDataTitleCount() + 1);
                writer.write(data);
//                writer.flush(); // 写入到文件
            } catch (Exception e) {
                e.printStackTrace();
            }

            //数据填充完,清空redis和cookie
            redisTemplate.delete("info:" + cookieValue);
            redisTemplate.delete("data:" + cookieValue);
            Cookie cookie = new Cookie("daniel", cookieValue);
            cookie.setPath("/"); //路径要相同
            cookie.setMaxAge(0); //生命周期设置为0 (清除cookie)
            response.addCookie(cookie);

            try {
                XSSFWorkbook workbook = new XSSFWorkbook(info.getPath());
                POIUtils.download(response, workbook, info.getFilename());
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                FileUtil.del(info.getPath());
            }

            return "<h1>导出数据完成, 请完成下载excel文件</h1>";
        } else {
            //没有daniel的cookie
            return "<h1>你还没有导入任何数据哦~</h1>";
        }

    }

    @GetMapping("/autodownload1")
    public String autoDownload1(HttpServletResponse response) throws IOException {

        String cookieValue = checkCookie("daniel", request);
        //有daniel的cookie
        if (cookieValue != null) {
            Task task = (Task) redisTemplate.opsForValue().get(cookieValue);
            //有daniel的cookie但是还没有导入数据
            if (task == null) {
                return "<h1>你还没有导入任何数据哦~</h1>";
            }
            //有daniel的cookie并且导入了数据
            List<List<Object>> data = task.getData1(); //导入的全部数据
            try (ExcelWriter writer = ExcelUtil.getWriter(task.getPath())) {
//                ClassPathResource resource = new ClassPathResource(task.getPath());
//                writer = ExcelUtil.getWriter(resource.getFile());
                //linux系统里
                writer.passRows(task.getDataTitleCount() + 1);
                writer.write(data);
            } catch (Exception e) {
                e.printStackTrace();
            }

            //数据填充完,清空redis和cookie
            redisTemplate.delete(cookieValue);
            Cookie cookie = new Cookie("daniel", cookieValue);
            cookie.setPath("/"); //路径要相同
            cookie.setMaxAge(0); //生命周期设置为0 (清除cookie)
            response.addCookie(cookie);

            try {
                XSSFWorkbook workbook = new XSSFWorkbook(task.getPath());
                POIUtils.download(response, workbook, task.getFilename());
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                FileUtil.del(task.getPath());
            }

            return "<h1>导出数据完成, 请完成下载excel文件</h1>";
        } else {
            //没有daniel的cookie
            return "<h1>你还没有导入任何数据哦~</h1>";
        }

    }
}
